﻿using MySqlConnector;
using System.Data;

namespace DAL
{
    /// <summary>
    /// MySql数据库访问辅助类
    /// </summary>
    public class MySqlHelper
    {
        /*
         * 实现功能
         * 1.定义数据库连接字符串
         * 2.定义查询方法，读取数据：ExecuteQuery，返回Datatable
         * 3.定义非查询方法，执行增删改：ExecuteNonQuery,返回受影响的行数
         * 4.定义获取单一值的方法：ExecuteScalar,返回一个对象或一个值
         */
        //1.定义MySql数据库连接字符串
        private static readonly string connString =
            "server=localhost;port=3306;" +
            "database=clubdb;" +
            "user=root;password=New123$;" +
            "charset=utf8mb4;SslMode=None;TreatTinyAsBoolean=false;AllowLoadLocalInfile=false";

        /// <summary>
        /// 2.定义查询方法，读取数据：ExecuteQuery，返回Datatable
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static DataTable ExecuteQuery(string sql, params MySqlParameter[] parameters)
        {
            //1.建立数据库连接
            using MySqlConnection conn = new(connString);
            //2.打开连接
            conn.Open();
            //3.构建命令
            using MySqlCommand cmd = new(sql, conn);
            //4.添加参数
            cmd.Parameters.AddRange(parameters);
            //5.构建数据适配器
            using MySqlDataAdapter adapter = new(cmd);
            //6.定义一个内存数据表
            DataTable dt = new();
            //7.用数据适配器填充数据表
            adapter.Fill(dt);
            return dt;
        }

        /// <summary>
        /// 定义非查询方法，执行增删改：ExecuteNonQuery,返回受影响的行数
        /// </summary>
        /// <param name="sql">查询语句（增删改）</param>
        /// <param name="parameters">参数集合</param>
        /// <returns>受影响的行数，执行成功返回1</returns>
        public static int ExecuteNonQuery(string sql, params MySqlParameter[] parameters)
        {
            using MySqlConnection con = new(connString);
            con.Open();
            using MySqlCommand cmd = new(sql, con);
            cmd.Parameters.AddRange(parameters);
            return cmd.ExecuteNonQuery();
        }

        /// <summary>
        /// 4.定义获取单一值的方法：ExecuteScalar,返回一个对象
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static object? ExecuteScalar(string sql, params object[] parameters)
        {
            using MySqlConnection conn = new(connString);
            conn.Open();
            using MySqlCommand cmd = new(sql, conn);
            cmd.Parameters.AddRange(parameters);
            return cmd.ExecuteScalar();
        }

        /// <summary>
        /// 4.定义获取单一值的方法：ExecuteScalar,返回自增ID- 用于新增后获取主键
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static long ExecuteNonQueryReturId(string sql, params object[] parameters)
        {
            using MySqlConnection conn = new(connString);
            conn.Open();
            using MySqlCommand cmd = new(sql + ";SELECT LAST_INSERT_ID();", conn);
            cmd.Parameters.AddRange(parameters);
            cmd.ExecuteNonQuery();
            return Convert.ToInt64(cmd.ExecuteScalar());
        }
    }
}
